import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_对学校就业教育_服务的评价分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，对学校就业教育/服务的评价分布 - 指标计算函数
    
    ## 指标说明
    该指标用于统计不同学历层次毕业生对学校各项就业教育/服务的满意度评价。
    计算每项服务(共7个子指标)的前三项(很满意、满意、基本满意)占比前五项(很满意、满意、基本满意、不满意、很不满意)的比例。
    结果按学历分组(本科、专科、硕士、博士)和总体分别统计。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "values": {
                    "生涯规划/就业指导课": 0.9545,
                    "职业规划大赛指导": 0.9231,
                    "职业咨询与辅导": 0.9123,
                    "招聘信息发布": 0.9342,
                    "校园招聘会/宣讲会": 0.9456,
                    "就业帮扶与推荐": 0.9023,
                    "就业手续办理": 0.9321
                }
            },
            {
                "group": "总体",
                "values": {
                    "生涯规划/就业指导课": 0.9423,
                    "职业规划大赛指导": 0.9234,
                    "职业咨询与辅导": 0.9156,
                    "招聘信息发布": 0.9332,
                    "校园招聘会/宣讲会": 0.9432,
                    "就业帮扶与推荐": 0.9123,
                    "就业手续办理": 0.9345
                }
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，对学校就业教育/服务的评价分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  
        
        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤GRADUATE_SHORT调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到GRADUATE_SHORT调研对象的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_codes = [
            'T00000424', 'T00024641', 'T00000425', 
            'T00024639', 'T00000426', 'T00000428', 'T00024640'
        ]
        question_sql = f"""
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code IN ({','.join(['%s'] * len(question_codes))}) AND is_del = 0
        """
        question_params = [valid_questionnaire_ids[0]] + question_codes
        questions = db.fetchall(question_sql, tuple(question_params))
        if not questions:
            raise ValueError(f"未找到指定问题编码的问题信息: {question_codes}")
        
        # 构建问题ID映射
        question_map = {q['wt_code']: q['id'] for q in questions}
        logger.info(f"找到问题ID映射: {question_map}")

        # 6. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 7. 定义学历列表和结果结构
        education_levels = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        result_data = []
        question_names = {
            'T00000424': '生涯规划/就业指导课',
            'T00024641': '职业规划大赛指导',
            'T00000425': '职业咨询与辅导',
            'T00024639': '招聘信息发布',
            'T00000426': '校园招聘会/宣讲会',
            'T00000428': '就业帮扶与推荐',
            'T00024640': '就业手续办理'
        }

        # 8. 按学历分组计算
        for education in education_levels:
            education_result = {'group': education, 'values': {}}
            
            for q_code, q_name in question_names.items():
                q_id = question_map.get(q_code)
                if not q_id:
                    logger.warning(f"未找到问题编码 {q_code} 的ID")
                    continue
                
                # 计算该问题的前三项占比
                sql = f"""
                SELECT 
                    (SUM(t1.c1) + SUM(t1.c2) + SUM(t1.c3)) / 
                    (SUM(t1.c1) + SUM(t1.c2) + SUM(t1.c3) + SUM(t1.c4) + SUM(t1.c5)) AS ratio
                FROM {answer_table} t1
                JOIN {student_table} s ON t1.target_no = s.target_no
                WHERE 
                    t1.cd_template_id = %s 
                    AND t1.wid = %s 
                    AND t1.ans_true = 1
                    AND s.shard_tb_key = %s
                    AND s.item_year = %s
                    AND s.education = %s
                """
                params = (
                    valid_questionnaire_ids[0], 
                    q_id, 
                    shard_tb_key, 
                    item_year, 
                    education
                )
                
                ratio_result = db.fetchone(sql, params)
                if ratio_result and ratio_result['ratio'] is not None:
                    education_result['values'][q_name] = float(ratio_result['ratio'])
                else:
                    education_result['values'][q_name] = 0.0
            
            result_data.append(education_result)

        # 9. 计算总体数据
        overall_result = {'group': '总体', 'values': {}}
        for q_code, q_name in question_names.items():
            q_id = question_map.get(q_code)
            if not q_id:
                continue
                
            sql = f"""
            SELECT 
                (SUM(t1.c1) + SUM(t1.c2) + SUM(t1.c3)) / 
                (SUM(t1.c1) + SUM(t1.c2) + SUM(t1.c3) + SUM(t1.c4) + SUM(t1.c5)) AS ratio
            FROM {answer_table} t1
            JOIN {student_table} s ON t1.target_no = s.target_no
            WHERE 
                t1.cd_template_id = %s 
                AND t1.wid = %s 
                AND t1.ans_true = 1
                AND s.shard_tb_key = %s
                AND s.item_year = %s
            """
            params = (
                valid_questionnaire_ids[0], 
                q_id, 
                shard_tb_key, 
                item_year
            )
            
            ratio_result = db.fetchone(sql, params)
            if ratio_result and ratio_result['ratio'] is not None:
                overall_result['values'][q_name] = float(ratio_result['ratio'])
            else:
                overall_result['values'][q_name] = 0.0
        
        result_data.append(overall_result)

        logger.info(f"指标 '各学历及总体，对学校就业教育/服务的评价分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_data
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，对学校就业教育/服务的评价分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，对学校就业教育/服务的评价分布",
            "code": 500,
            "error": str(e)
        }